package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Util.JdbcUtil;
import Entity.*;

public class ListPayProveDao {
	//查询所有 预付款存入凭证
	public List<PayProveEntity> select(){
		List<PayProveEntity>list = new ArrayList<PayProveEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from pay;";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				PayProveEntity pay = new PayProveEntity();
				pay.setId(rs.getInt(1));
				pay.setNum(rs.getInt(2));
				pay.setName(rs.getString(3));
				pay.setCode(rs.getInt(4));
				pay.setTime(rs.getString(5));
				pay.setPay_total(rs.getDouble(6));
				pay.setSurplus(rs.getDouble(7));
				pay.setOperator(rs.getString(8));
				pay.setProject(rs.getInt(9));
				pay.setNullify(rs.getInt(10));
				list.add(pay);
			}
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	//作废该凭证
	public void nullify(int id,int state){
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "update pay set nullify="+state+" where id="+id+";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//分页
	//得出数据总行数
	public int select_rows(){
		int rows = 0;
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select COUNT(id) num from pay";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			rows = rs.getInt(1);
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rows;
	}
	//分页
	//获得一定时间段内的数据总行数
	public int select_rowsBy_time(String begin_time,String end_time){
		int rows = 0;
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select COUNT(id) num from pay where time>='"+begin_time+"' and time<='"+end_time+"' order by time desc;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			rows = rs.getInt(1);
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rows;
	}
	public List<PayProveEntity> select_PagesRows(int begin, int pages_rows){
		List<PayProveEntity>list = new ArrayList<PayProveEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from pay limit "+begin+","+pages_rows+";";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				PayProveEntity pay = new PayProveEntity();
				pay.setId(rs.getInt(1));
				pay.setNum(rs.getInt(2));
				pay.setName(rs.getString(3));
				pay.setCode(rs.getInt(4));
				pay.setTime(rs.getString(5));
				pay.setPay_total(rs.getDouble(6));
				pay.setSurplus(rs.getDouble(7));
				pay.setOperator(rs.getString(8));
				pay.setProject(rs.getInt(9));
				pay.setNullify(rs.getInt(10));
				list.add(pay);
			}
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	//查询一定时间段内 加 翻页功能，的所有数据
	public List<PayProveEntity> selectBytime_PagesRows(int begin, int pages_rows, String begin_time, String end_time){
		List<PayProveEntity>list = new ArrayList<PayProveEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from pay where time>='"+begin_time+"' and time<='"+end_time+"' order by time ASC limit "+begin+","+pages_rows+";";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				PayProveEntity pay = new PayProveEntity();
				pay.setId(rs.getInt(1));
				pay.setNum(rs.getInt(2));
				pay.setName(rs.getString(3));
				pay.setCode(rs.getInt(4));
				pay.setTime(rs.getString(5));
				pay.setPay_total(rs.getDouble(6));
				pay.setSurplus(rs.getDouble(7));
				pay.setOperator(rs.getString(8));
				pay.setProject(rs.getInt(9));
				pay.setNullify(rs.getInt(10));
				list.add(pay);
			}
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	//查询所有信息的结束时间
	public String select_end_time(){
		String end_time = "";
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from pay ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//判断账户数据是否为空
			if(rs.last()){
				end_time = rs.getString(5);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return end_time;
	}
	//查询所有信息的开始时间
	public String select_begin_time(){
		String begin_time = "";
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from pay ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//判断账户数据是否为空
			if(rs.first()){
				begin_time = rs.getString(5);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return begin_time;
	}
	//查询在一定时间范围内的所有信息
	public List<PayProveEntity> selectBytime(String begin_time, String end_time){
		List<PayProveEntity>list = new ArrayList<PayProveEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from pay where time >=;";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				PayProveEntity pay = new PayProveEntity();
				pay.setId(rs.getInt(1));
				pay.setNum(rs.getInt(2));
				pay.setName(rs.getString(3));
				pay.setCode(rs.getInt(4));
				pay.setTime(rs.getString(5));
				pay.setPay_total(rs.getDouble(6));
				pay.setSurplus(rs.getDouble(7));
				pay.setOperator(rs.getString(8));
				pay.setProject(rs.getInt(9));
				pay.setNullify(rs.getInt(10));
				list.add(pay);
			}
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 改变指定用户的余额
	 * @param money
	 */
	public void change_money(int id,double money){
		String sql = "update user set surplus="+money+" where code="+id+";";
		try {
			Connection conn = JdbcUtil.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获得指定 ID 的用户的 当前 余额
	 * @param id
	 * @return
	 */
	public double getUserBalance(int id){
		double money = 0;
		String sql = "select surplus from user where code="+id+";";
		try {
			Connection conn = JdbcUtil.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//判断账户数据是否为空
			if(rs.next()){
				money = rs.getDouble(1);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return money;
	}
}
